using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using DM4;
using sscore;
using sscore.Utils;
using System.Threading;

namespace DMN_MailSender
{
    public class WorkWithDbData
    {
        private readonly string _connectionString;
        private readonly LogHandler _writeLog;

        public WorkWithDbData(string connectionString, LogHandler writeLog = null)
        {
            _connectionString = connectionString;
            _writeLog = writeLog;
        }

        public void UpdateMailStatus(MailSenderWorkerParameters mailParams)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    string sql = String.Format("UPDATE [{0}] SET [{1}] = {2} ,[{3}] = '{4}' ,[{5}] = '{6}'  WHERE [{7}] = @{7}",
                        Tn.MailSend,
                        CnMailSend.AttemptsPerformed, mailParams.AttemptsPerformed,
                        CnMailSend.Result, mailParams.Result,
                        CnMailSend.ResultMessage, mailParams.ResultMessage,
                        CnMailSend.Id);

                    using (SqlCommand sc = new SqlCommand(sql, conn))
                    {
                        SqlParameter dateParameter = sc.Parameters.Add("@" + CnMailSend.Id, SqlDbType.DateTime);
                        dateParameter.Value = mailParams.StartTime;
                        sc.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                string m = String.Format("Error of UpdateMailStatus [{0}] from account [{1}] to [{2}] on template [{3}]. {4}"
                    , mailParams.StartTime.ToString(MailSenderConsts.IdFormat), mailParams.AccFrom.Mail
                    , mailParams.AccTo
                    , mailParams.MailTemplate.Name
                    , ex.Message);

                WriteLog(m, MessageType.Error);
                throw new Exception(m);
            }
        }

        public MailAccount GetMailAccount(string name)
        {
            return GetMailAccount(-1, name);
        }

        public MailAccount GetMailAccount(int id, string name = "")
        {
            MailAccount ma = new MailAccount();

            string sql =
                name == ""
                ? String.Format("select * from {0} where {1} = {2}", Tn.MailAccounts, CnMailAccounts.Id, id)
                : String.Format("select * from {0} where LTRIM(RTRIM(UPPER({1}))) = '{2}'", Tn.MailAccounts, CnMailAccounts.Mail, name.Trim().ToUpper());

            WriteLog(sql);

            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                using (SqlCommand sel = new SqlCommand(sql, conn))
                {
                    using (SqlDataReader reader = sel.ExecuteReader())
                    {
                        if (reader.HasRows)
                            try
                            {
                                reader.Read();
                                ma.Id = SysUtils.ConvertFromDBVal<int>(reader[CnMailAccounts.Id]);
                                ma.Mail = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.Mail]);
                                ma.SendServer = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.SendServer]);
                                ma.SendPort = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.SendPort]);
                                ma.SendUser = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.SendUser]);
                                ma.SendPassword = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.SendPassword]);
                                ma.SendUseSSL = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.SendUseSsl]) == Consts.TRUE;

                                ma.ReceiveServer = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.ReceiveServer]);
                                ma.ReceivePort = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.ReceivePort]);
                                ma.ReceiveUser = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.ReceiveUser]);
                                ma.ReceivePassword = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.ReceivePassword]);
                                ma.ReceiveUseSSL = SysUtils.ConvertFromDBVal<string>(reader[CnMailAccounts.ReceiveUseSsl]) == Consts.TRUE;
                            }
                            catch (Exception ex)
                            {
                                WriteLog(
                                    String.Format("Error occurred while GetMailAccount: Id [{0}], name [{1}]. {2}", id, name,
                                                  ex.Message), MessageType.Error);
                            }
                    }
                }
            }
            return ma;
        }

        public MailSendTemplate GetMailSendTemplateInfo(string name)
        {
            return GetMailSendTemplateInfo(-1, name);
        }

        public MailSendTemplate GetMailSendTemplateInfo(int id, string name = "")
        {
            MailSendTemplate si = new MailSendTemplate();

            string sql =
                name == ""
                ? String.Format("select * from {0} where {1} = {2}", Tn.MailSendTemplate, CnMailSendTemplate.Id, id)
                : String.Format("select * from {0} where LTRIM(RTRIM(UPPER({1}))) = '{2}'", Tn.MailSendTemplate, CnMailSendTemplate.Name, name.Trim().ToUpper());

            WriteLog(sql);
            using (SqlConnection conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                using (SqlCommand sel = new SqlCommand(sql, conn))
                {
                    using (SqlDataReader reader = sel.ExecuteReader())
                    {
                        if (reader.HasRows)
                            try
                            {
                                reader.Read();
                                si.Id = SysUtils.ConvertFromDBVal<int>(reader[CnMailSendTemplate.Id]);
                                si.Name = SysUtils.ConvertFromDBVal<string>(reader[CnMailSendTemplate.Name]);
                                si.Subject = SysUtils.ConvertFromDBVal<string>(reader[CnMailSendTemplate.Subject]);
                                si.Body = SysUtils.ConvertFromDBVal<string>(reader[CnMailSendTemplate.Body]);
                            }
                            catch (Exception ex)
                            {
                                WriteLog(
                                    String.Format("Error occurred while GetMailSendTemplateInfo: Id [{0}], name [{1}]. {2}",
                                                  id, name, ex.Message), MessageType.Error);
                            }
                    }
                }
            }
            return si;
        }

        public List<MailSenderWorkerParameters> CreateMailSenderParameters(List<MailSenderParameters> param)
        {
            List<MailSenderWorkerParameters> resultList = new List<MailSenderWorkerParameters>();
            foreach (MailSenderParameters paramItem in param)
            {
                Thread.Sleep(10); //for unique using MailSenderWorkerParameters Id (DateTime)
                resultList.Add(CreateMailSenderParameters(paramItem));
            }

            return resultList;
        }
        public MailSenderWorkerParameters CreateMailSenderParameters(MailSenderParameters param)
        {
            MailSenderWorkerParameters msp;

            MailAccount accountFrom = (param.AccFromId == DM4Consts.DefaultNULLId)
                    ? GetMailAccount(param.AccFrom)
                    : GetMailAccount(param.AccFromId);

            MailSendTemplate mailTemplate = new MailSendTemplate();
            if (param.MailTemplateId == DM4Consts.DefaultNULLId)
            {
                if (!String.IsNullOrEmpty(param.MailTemplateName))
                    mailTemplate = GetMailSendTemplateInfo(param.MailTemplateName);
            }
            else mailTemplate = GetMailSendTemplateInfo(param.MailTemplateId);

            if (param.Macros == null || param.Macros.Count == 0)
                msp = CreateMailSenderParameters(accountFrom, param.MailTo, mailTemplate,
                    param.Subject, param.Body,
                    param.Attempts, 0, param.Timeout);
            else
                msp = CreateMailSenderParameters(accountFrom, param.MailTo, mailTemplate,
                    param.Macros,
                    param.Attempts, param.Timeout);

            return msp;
        }


        public MailSenderWorkerParameters CreateMailSenderParameters(string accountFromName, string mailTo, string mailTemplateName, string subjectText, string bodyText, int attemptsTotal = 1, int attemptsPerformed = 0, int timeout = MailSenderConsts.Timeout)
        {
            return CreateMailSenderParameters(GetMailAccount(accountFromName), mailTo, GetMailSendTemplateInfo(mailTemplateName), subjectText, bodyText, attemptsTotal, attemptsPerformed, timeout);
        }

        public MailSenderWorkerParameters CreateMailSenderParameters(int accountFromId, string mailTo, int mailTemplateId, string subjectText, string bodyText, int attemptsTotal = 1, int attemptsPerformed = 0, int timeout = MailSenderConsts.Timeout)
        {
            return CreateMailSenderParameters(GetMailAccount(accountFromId), mailTo, GetMailSendTemplateInfo(mailTemplateId), subjectText, bodyText, attemptsTotal, attemptsPerformed, timeout);
        }

        public MailSenderWorkerParameters CreateMailSenderParameters(MailAccount accountFrom, string mailTo, MailSendTemplate mailTemplate, string subjectText, string bodyText, int attemptsTotal = 1, int attemptsPerformed = 0, int timeout = MailSenderConsts.Timeout)
        {
            MailSenderWorkerParameters msp = new MailSenderWorkerParameters(DateTime.Now, accountFrom, mailTo, mailTemplate, subjectText, bodyText, attemptsTotal, attemptsPerformed, timeout);
            InsertMailSenderParameters(msp);
            return msp;
        }

        public MailSenderWorkerParameters CreateMailSenderParameters(int accountFromId, string mailTo, int mailTemplateId, Dictionary<string, string> macros, int attempts = 1, int timeout = MailSenderConsts.Timeout)
        {
            return CreateMailSenderParameters(GetMailAccount(accountFromId), mailTo, GetMailSendTemplateInfo(mailTemplateId), macros, attempts, timeout);
        }

        public MailSenderWorkerParameters CreateMailSenderParameters(MailAccount accountFrom, string mailTo, MailSendTemplate mailTemplate, Dictionary<string, string> macros, int attempts = 1, int timeout = MailSenderConsts.Timeout)
        {
            MailSenderWorkerParameters msp = new MailSenderWorkerParameters(accountFrom, mailTo, mailTemplate, macros, attempts, timeout);
            InsertMailSenderParameters(msp);
            return msp;
        }

        private void InsertMailSenderParameters(MailSenderWorkerParameters msp)
        {           
            try
            {
                string sql = String.Format("if not exists (select * from [{0}] where [{1}] = @{1}) "
                    + "INSERT INTO [{0}] "
                    + "([{1}] ,[{2}],[{3}],"
                    + "[{4}],[{5}],[{6}],"
                    + "[{7}],[{8}],[{9}],"
                    + "[{10}],[{11}]) "
                    + "VALUES (@{1}, {12},'{13}',"
                    + "{14},@{5},@{6},"
                    + "{15},{16},{17}"
                    + ",'{18}','{19}')",
                    Tn.MailSend,
                    CnMailSend.Id, CnMailSend.MailAccountId, CnMailSend.AccountTo,
                    CnMailSend.MailTemplateId, CnMailSend.Subject, CnMailSend.Body,
                    CnMailSend.Timeout, CnMailSend.AttemptsTotal, CnMailSend.AttemptsPerformed,
                    CnMailSend.Result, CnMailSend.ResultMessage,
                    msp.AccFrom.Id, msp.AccTo,
                    msp.MailTemplate.Id, 
                    msp.Timeout, msp.AttemptsTotal, msp.AttemptsPerformed,
                    msp.Result, msp.ResultMessage.Replace("'", "''"));

                using (SqlConnection conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    using (SqlCommand sc = new SqlCommand(sql, conn))
                    {

                        SqlParameter dateParameter = sc.Parameters.Add("@" + CnMailSend.Id, SqlDbType.DateTime);
                        dateParameter.Value = msp.StartTime;

                        SqlParameter paramSubject = sc.Parameters.Add("@" + CnMailSend.Subject, SqlDbType.NVarChar);
                        paramSubject.Value = msp.SubjectText;
                        paramSubject.Size = -1;

                        SqlParameter paramBody = sc.Parameters.Add("@" + CnMailSend.Body, SqlDbType.NVarChar);
                        paramBody.Value = msp.SubjectText;
                        paramBody.Size = -1;

                        sc.ExecuteNonQuery();
                    }
                }

            }
            catch (Exception ex)
            {
                WriteLog(String.Format("Error occurred while SaveMailSenderParameters function execute. {0}", ex.Message), MessageType.Error);
            }
        }

        public List<MailSenderWorkerParameters> GetNotEndedMail()
        {
            List<MailSenderWorkerParameters> resultList = new List<MailSenderWorkerParameters>();
            string sql = String.Format("select * from {0} where [{1}] != '{4}'  and [{2}] < [{3}]  and  [{3}] != 0  "
                , Tn.MailSend
                , CnMailSend.Result
                , CnMailSend.AttemptsPerformed, CnMailSend.AttemptsTotal
                , Consts.TRUE);
            WriteLog(sql);
            try
            {
                using (SqlConnection conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    using (SqlCommand sel = new SqlCommand(sql, conn))
                    {
                        using (SqlDataReader reader = sel.ExecuteReader())
                        {

                            if (reader.HasRows)
                                while (reader.Read())
                                {
                                    MailSenderWorkerParameters msp = new MailSenderWorkerParameters(
                                        Convert.ToDateTime(reader[CnMailSend.Id])
                                        , GetMailAccount(SysUtils.ConvertFromDBVal<int>(reader[CnMailSend.MailAccountId]))
                                        , SysUtils.ConvertFromDBVal<string>(reader[CnMailSend.AccountTo])
                                        , GetMailSendTemplateInfo(SysUtils.ConvertFromDBVal<int>(reader[CnMailSend.MailTemplateId]))
                                        , SysUtils.ConvertFromDBVal<string>(reader[CnMailSend.Subject])
                                        , SysUtils.ConvertFromDBVal<string>(reader[CnMailSend.Body])
                                        , SysUtils.ConvertFromDBVal<int>(reader[CnMailSend.AttemptsTotal])
                                        , SysUtils.ConvertFromDBVal<int>(reader[CnMailSend.AttemptsPerformed])
                                        , SysUtils.ConvertFromDBVal<int>(reader[CnMailSend.Timeout]));

                                    resultList.Add(msp);
                                }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                WriteLog(String.Format("Error occurred while GetNotEndedMail function execute. {0}", ex.Message), MessageType.Error);
            }
            return resultList;
        }

        private void WriteLog(string aMessage, MessageType type = MessageType.Info)
        {
            if (_writeLog != null)
                _writeLog(aMessage, type);
        }

    }
}
